<?php
/**
 * 修复exams表结构工具
 * 解决exam_projects.php页面的数据库字段缺失问题
 */

// 错误报告设置
error_reporting(E_ALL);
ini_set('display_errors', 1);

echo "🔧 考试项目表结构修复工具\n";
echo "=" . str_repeat("=", 50) . "\n";

try {
    // 数据库连接
    require_once 'database.php';

    if (!isset($conn) || !($conn instanceof mysqli)) {
        throw new Exception("数据库连接失败");
    }

    echo "✅ 数据库连接成功\n\n";

    // 1. 检查当前表结构
    echo "📋 检查当前exams表结构...\n";
    $result = $conn->query("DESCRIBE exams");

    if (!$result) {
        throw new Exception("无法获取表结构: " . $conn->error);
    }

    $existing_columns = [];
    echo "当前字段列表:\n";
    while ($row = $result->fetch_assoc()) {
        $existing_columns[] = $row['Field'];
        echo "  - {$row['Field']} ({$row['Type']})\n";
    }
    echo "\n";

    // 2. 检查现有数据
    $count_result = $conn->query("SELECT COUNT(*) as count FROM exams");
    $count = $count_result ? $count_result->fetch_assoc()['count'] : 0;
    echo "📊 现有记录数量: {$count}\n\n";

    // 3. 定义需要的字段
    $required_fields = [
        'status' => [
            'definition' => "ENUM('active', 'inactive', 'completed', 'cancelled') DEFAULT 'active'",
            'comment' => '项目状态'
        ],
        'created_by' => [
            'definition' => "VARCHAR(100)",
            'comment' => '创建者'
        ],
        'exam_date' => [
            'definition' => "DATE",
            'comment' => '考试日期'
        ],
        'updated_at' => [
            'definition' => "TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP",
            'comment' => '更新时间'
        ],
        'description' => [
            'definition' => "TEXT",
            'comment' => '项目描述'
        ]
    ];

    // 4. 添加缺失字段
    echo "🔧 开始修复表结构...\n";
    $added_fields = 0;

    foreach ($required_fields as $field => $config) {
        if (!in_array($field, $existing_columns)) {
            $sql = "ALTER TABLE exams ADD COLUMN {$field} {$config['definition']} COMMENT '{$config['comment']}'";

            if ($conn->query($sql)) {
                echo "✅ 成功添加字段: {$field}\n";
                $added_fields++;
            } else {
                echo "❌ 添加字段 {$field} 失败: " . $conn->error . "\n";
            }
        } else {
            echo "ℹ️ 字段 {$field} 已存在，跳过\n";
        }
    }

    echo "\n";

    // 5. 更新现有记录的默认值
    if ($count > 0 && in_array('status', $existing_columns) === false && $added_fields > 0) {
        echo "🔄 更新现有记录的默认值...\n";

        // 为现有记录设置默认状态
        $update_sql = "UPDATE exams SET status = 'active' WHERE status IS NULL OR status = ''";
        if ($conn->query($update_sql)) {
            echo "✅ 更新现有记录状态成功\n";
        }

        // 为现有记录设置默认创建者
        $update_creator_sql = "UPDATE exams SET created_by = 'system' WHERE created_by IS NULL OR created_by = ''";
        if ($conn->query($update_creator_sql)) {
            echo "✅ 更新现有记录创建者成功\n";
        }

        // 为现有记录设置默认考试日期（如果没有的话）
        $update_date_sql = "UPDATE exams SET exam_date = DATE_ADD(NOW(), INTERVAL 30 DAY) WHERE exam_date IS NULL";
        if ($conn->query($update_date_sql)) {
            echo "✅ 更新现有记录考试日期成功\n";
        }
    }

    // 6. 验证修复结果
    echo "\n📋 验证修复结果...\n";
    $verify_result = $conn->query("DESCRIBE exams");
    $final_columns = [];

    while ($row = $verify_result->fetch_assoc()) {
        $final_columns[] = $row['Field'];
    }

    echo "修复后字段列表:\n";
    foreach ($final_columns as $column) {
        echo "  - {$column}\n";
    }

    // 7. 测试查询
    echo "\n🧪 测试查询语句...\n";
    $test_sql = "
        SELECT 
            e.id,
            e.exam_name,
            e.exam_date,
            e.status,
            e.created_by,
            e.created_at
        FROM exams e 
        LIMIT 3
    ";

    $test_result = $conn->query($test_sql);
    if ($test_result) {
        echo "✅ 查询测试成功\n";
        echo "示例数据:\n";
        while ($row = $test_result->fetch_assoc()) {
            echo "  - ID: {$row['id']}, 名称: {$row['exam_name']}, 状态: {$row['status']}\n";
        }
    } else {
        echo "❌ 查询测试失败: " . $conn->error . "\n";
    }

    echo "\n🎉 表结构修复完成！\n";
    echo "=" . str_repeat("=", 50) . "\n";
    echo "📊 修复统计:\n";
    echo "  - 添加字段数: {$added_fields}\n";
    echo "  - 现有记录数: {$count}\n";
    echo "  - 总字段数: " . count($final_columns) . "\n";
    echo "\n✅ 现在可以正常使用exam_projects.php页面了！\n";

} catch (Exception $e) {
    echo "❌ 修复过程中发生错误: " . $e->getMessage() . "\n";
    echo "请检查数据库连接和权限设置。\n";
    exit(1);
}

// 关闭数据库连接
if (isset($conn)) {
    $conn->close();
}
?>